package com.example.sqliteviewer;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.os.AsyncTask;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

/**
 * CSV导入工具类
 */
public class CsvImportUtils {

    /**
     * CSV导入监听器接口
     */
    public interface CsvImportListener {
        void onImportProgress(int progress, int total);
        void onImportComplete(String tableName, int rowsImported);
        void onImportError(String error);
    }

    /**
     * 从CSV文件导入数据到新表
     *
     * @param context      上下文
     * @param databasePath 数据库路径
     * @param csvUri       CSV文件的Uri
     * @param tableName    要创建的表名
     * @param listener     导入监听器
     */
    public static void importCsvToNewTable(Context context, String databasePath, Uri csvUri, String tableName, CsvImportListener listener) {
        new CsvImportTask(context, databasePath, csvUri, tableName, listener).execute();
    }

    /**
     * CSV导入异步任务
     */
    private static class CsvImportTask extends AsyncTask<Void, Integer, Boolean> {
        private final Context context;
        private final String databasePath;
        private final Uri csvUri;
        private final String tableName;
        private final CsvImportListener listener;
        private String errorMessage;
        private int rowsImported = 0;

        public CsvImportTask(Context context, String databasePath, Uri csvUri, String tableName, CsvImportListener listener) {
            this.context = context;
            this.databasePath = databasePath;
            this.csvUri = csvUri;
            this.tableName = tableName;
            this.listener = listener;
        }

        @Override
        protected Boolean doInBackground(Void... voids) {
            SQLiteDatabase database = null;
            BufferedReader reader = null;
            
            try {
                // 打开数据库
                database = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.OPEN_READWRITE);
                
                // 读取CSV文件
                InputStream inputStream = context.getContentResolver().openInputStream(csvUri);
                reader = new BufferedReader(new InputStreamReader(inputStream));
                
                // 读取标题行
                String headerLine = reader.readLine();
                if (headerLine == null) {
                    errorMessage = "CSV文件为空";
                    return false;
                }
                
                // 解析列名
                String[] columnNames = parseCSVLine(headerLine);
                if (columnNames.length == 0) {
                    errorMessage = "无法解析CSV标题行";
                    return false;
                }
                
                // 创建表
                StringBuilder createTableSQL = new StringBuilder();
                createTableSQL.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
                
                // 添加列定义
                for (int i = 0; i < columnNames.length; i++) {
                    if (i > 0) {
                        createTableSQL.append(", ");
                    }
                    // 使用TEXT类型，后续可以根据数据类型推断优化
                    createTableSQL.append('"').append(columnNames[i].replace("\"", "")).append('"').append(" TEXT");
                }
                createTableSQL.append(")");
                
                // 执行创建表SQL
                database.execSQL(createTableSQL.toString());
                
                // 开始事务
                database.beginTransaction();
                
                // 准备插入语句
                StringBuilder insertSQL = new StringBuilder();
                insertSQL.append("INSERT INTO ").append(tableName).append(" (");
                
                for (int i = 0; i < columnNames.length; i++) {
                    if (i > 0) {
                        insertSQL.append(", ");
                    }
                    insertSQL.append('"').append(columnNames[i].replace("\"", "")).append('"');
                }
                
                insertSQL.append(") VALUES (");
                
                for (int i = 0; i < columnNames.length; i++) {
                    if (i > 0) {
                        insertSQL.append(", ");
                    }
                    insertSQL.append("?");
                }
                
                insertSQL.append(")");
                
                // 读取数据行
                String line;
                int lineCount = 0;
                int totalLines = countLines(csvUri) - 1; // 减去标题行
                
                while ((line = reader.readLine()) != null) {
                    // 解析数据行
                    String[] values = parseCSVLine(line);
                    
                    // 确保值的数量与列名数量一致
                    if (values.length == columnNames.length) {
                        // 执行插入
                        database.execSQL(insertSQL.toString(), values);
                        rowsImported++;
                    }
                    
                    // 更新进度
                    lineCount++;
                    publishProgress(lineCount, totalLines);
                }
                
                // 提交事务
                database.setTransactionSuccessful();
                return true;
                
            } catch (Exception e) {
                e.printStackTrace();
                errorMessage = "导入失败: " + e.getMessage();
                return false;
            } finally {
                // 结束事务
                if (database != null) {
                    try {
                        database.endTransaction();
                        database.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                
                // 关闭读取器
                if (reader != null) {
                    try {
                        reader.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        @Override
        protected void onProgressUpdate(Integer... values) {
            if (listener != null && values.length >= 2) {
                listener.onImportProgress(values[0], values[1]);
            }
        }

        @Override
        protected void onPostExecute(Boolean success) {
            if (success) {
                if (listener != null) {
                    listener.onImportComplete(tableName, rowsImported);
                }
            } else {
                if (listener != null) {
                    listener.onImportError(errorMessage);
                }
            }
        }
        
        /**
         * 解析CSV行
         * 处理引号包围的字段和逗号分隔
         */
        private String[] parseCSVLine(String line) {
            List<String> tokens = new ArrayList<>();
            StringBuilder sb = new StringBuilder();
            boolean inQuotes = false;
            
            for (int i = 0; i < line.length(); i++) {
                char c = line.charAt(i);
                
                if (c == '"') {
                    // 检查是否为转义的引号 ("")
                    if (i + 1 < line.length() && line.charAt(i + 1) == '"') {
                        sb.append('"');
                        i++; // 跳过下一个引号
                    } else {
                        inQuotes = !inQuotes;
                    }
                } else if (c == ',' && !inQuotes) {
                    // 找到字段分隔符
                    tokens.add(sb.toString());
                    sb.setLength(0);
                } else {
                    sb.append(c);
                }
            }
            
            // 添加最后一个字段
            tokens.add(sb.toString());
            
            return tokens.toArray(new String[0]);
        }
        
        /**
         * 计算CSV文件的行数
         */
        private int countLines(Uri csvUri) {
            BufferedReader reader = null;
            try {
                InputStream inputStream = context.getContentResolver().openInputStream(csvUri);
                reader = new BufferedReader(new InputStreamReader(inputStream));
                
                int count = 0;
                while (reader.readLine() != null) {
                    count++;
                }
                return count;
            } catch (IOException e) {
                e.printStackTrace();
                return 0;
            } finally {
                if (reader != null) {
                    try {
                        reader.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
}